﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace SGArchiveSystem7xDB.SQL
{
    public class T_ArchiveAjSQL
    {
        static SGArchiveSystem7xEntities conn;
        /// <summary>
        /// 统计档案类型数
        /// </summary>
        /// <returns></returns>
        public static DataTable GetArchivlist()
        {
            conn = new SGArchiveSystem7xEntities();
            SqlConnection connstring = new SqlConnection();

            try
            {
                connstring.ConnectionString = conn.Database.Connection.ConnectionString;
                if (connstring.State != ConnectionState.Open)
                {
                    connstring.Open();
                }
                string sql = "select count(aj.ID) as 数量 ,t.AR_NAME as 档案类型 from T_ArchiveAj as aj ,T_ArchiveTree t where aj.ARID = t.AR_ID group by t.AR_ID ,t.AR_NAME";
                SqlCommand cmd = new SqlCommand(sql, connstring);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                //List<string> list = new List<string>();
                //for (int i = 0; i < dt.Rows.Count; i++)
                //{
                //    list.Add(dt.Rows[i].ItemArray[0].ToString());
                //    list.Add(dt.Rows[i].ItemArray[1].ToString());
                //}


                connstring.Close();
                connstring.Dispose();
                return dt;

            }

            catch (Exception ex)
            {
                connstring.Close();
                connstring.Dispose();
                return null;
            }
            finally
            {
                connstring.Close();
                connstring.Dispose();
            }

        }

        /// <summary>
        /// 档案、电子文件统计
        /// </summary>
        /// <returns></returns>
        public static DataTable GetArchivCountlist()
        {

            conn = new SGArchiveSystem7xEntities();
            SqlConnection connstring = new SqlConnection();

            try
            {
                connstring.ConnectionString = conn.Database.Connection.ConnectionString;
                if (connstring.State != ConnectionState.Open)
                {
                    connstring.Open();
                }
                string sql = @"select COUNT(aj.ID) as 档案总数量 , COUNT(aj.DZWJ) as 电子文件数 from T_ArchiveAj as aj";
                SqlCommand cmd = new SqlCommand(sql, connstring);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                connstring.Close();
                connstring.Dispose();
                return dt;

            }

            catch (Exception ex)
            {
                connstring.Close();
                connstring.Dispose();
                return null;
            }
            finally
            {
                connstring.Close();
                connstring.Dispose();
            }

        }
        /// <summary>
        /// 借阅统计
        /// </summary>
        /// <returns></returns>
        public static DataTable GetBorrowList()
        {
            conn = new SGArchiveSystem7xEntities();
            SqlConnection connstring = new SqlConnection();
            try
            {
                connstring.ConnectionString = conn.Database.Connection.ConnectionString;
                if (connstring.State != ConnectionState.Open)
                {
                    connstring.Open();
                }
                #region sql
                string now1 = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:01";
                string now2 = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
                string sql = @"select COUNT(*) ,
                    (select COUNT(*) from t_archiveaj v where v.kfzt <> 1) as 不在库 ,
                    (select COUNT(*) from t_archiveaj v where v.kfzt = 1) as 在库 ,
                    (select COUNT(*) from t_archiveaj v where (v.d01 >'" +  now1 + "' and v.d01 <'" + now2 +
                    "')) as 今日出库, (select COUNT(*) from t_archiveaj v where (v.d01 >'" + now1 + "' and v.d01 <'" + now2 +
                    "')) as 今日入库  from t_archiveaj";
                #endregion
                SqlCommand cmd = new SqlCommand(sql, connstring);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                connstring.Close();
                connstring.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                connstring.Close();
                connstring.Dispose();
                return null;
            }
            finally
            {
                connstring.Close();
                connstring.Dispose();
            }
        }


        public static string GetNoSQList()
        {
            conn = new SGArchiveSystem7xEntities();
            SqlConnection connstring = new SqlConnection();

            try
            {
                connstring.ConnectionString = conn.Database.Connection.ConnectionString;
                if (connstring.State != ConnectionState.Open)
                {
                    connstring.Open();
                }
                string sql = @"select COUNT(*) as count from T_ArchiveJK where JK_Status=2 and (JK_CLR is null or JK_CLR = '')";
                SqlCommand cmd = new SqlCommand(sql, connstring);
                return cmd.ExecuteScalar().ToString();
                //SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //DataTable dt = new DataTable();
                //adapter.Fill(dt);
                //connstring.Close();
                //connstring.Dispose();
                //return dt;

            }

            catch (Exception ex)
            {
                connstring.Close();
                connstring.Dispose();
                return "0";
            }
            finally
            {
                connstring.Close();
                connstring.Dispose();
            }
        }

        /// <summary>
        /// 申请借阅（全部的）
        /// </summary>
        /// <returns></returns>
        public static DataTable Get_ArchivsApplication()
        {
            conn = new SGArchiveSystem7xEntities();
            SqlConnection connstring = new SqlConnection();
            try
            {
                connstring.ConnectionString = conn.Database.Connection.ConnectionString;
                if (connstring.State != ConnectionState.Open)
                {
                    connstring.Open();
                }
                string sql = "select top 10 * from t_archiveaj order by sjjlrq desc";

                ///string sql = "select BU_BUSERNAME , BU_LYMD ,BI_ATYPE,BI_DH, BI_YJGHDATE,BI_TM from V_BInfo where [BI_STATUS] = 0";
                SqlCommand cmd = new SqlCommand(sql, connstring);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                connstring.Close();
                connstring.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                connstring.Close();
                connstring.Dispose();
                return null;
            }
            finally
            {
                connstring.Close();
                connstring.Dispose();
            }
        }


        /// <summary>
        /// 今日借阅到期（全部的）
        /// </summary>
        /// <returns></returns>
        public static DataTable Get_ArchivsExpiration()
        {
            conn = new SGArchiveSystem7xEntities();
            SqlConnection connstring = new SqlConnection();
            try
            {
                connstring.ConnectionString = conn.Database.Connection.ConnectionString;
                if (connstring.State != ConnectionState.Open)
                {
                    connstring.Open();
                }

                string sql = "select top 10 * from t_archiveaj where (d02 is not null or d02 <> '') order by d02 desc";
                // string sql = @"select  BU_BUSERNAME , BU_LYMD ,BI_ATYPE,BI_DH, BI_YJGHDATE,BI_TM from V_BInfo v where[BI_STATUS] = 1 AND BI_YJGHDATE between " + "'"+ DateTime.Now.ToString("yyyy-MM-dd 00:00:00")+"'"
                //     +  " and " + "'"+ DateTime.Now.ToString("yyyy-MM-dd 23:59:59")+"'";
                SqlCommand cmd = new SqlCommand(sql, connstring);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                connstring.Close();
                connstring.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                connstring.Close();
                connstring.Dispose();
                return null;
            }
            finally
            {
                connstring.Close();
                connstring.Dispose();
            }
        }


        /// <summary>
        /// 当前借阅逾期（全部的）
        /// </summary>
        /// <returns></returns>
        public static DataTable Get_ArchivsOverdue()
        {
            conn = new SGArchiveSystem7xEntities();
            SqlConnection connstring = new SqlConnection();
            try
            {
                connstring.ConnectionString = conn.Database.Connection.ConnectionString;
                if (connstring.State != ConnectionState.Open)
                {
                    connstring.Open();
                }
                string sql = "select top 10 * from t_archiveaj where (d02 is not null or d02 <> '') order by d01 desc";
                //string sql = "select BU_BUSERNAME , BU_LYMD ,BI_ATYPE,BI_DH, BI_YJGHDATE,BI_TM from V_BInfo where [BI_STATUS] = 1 and BU_TYPE = 2 AND BI_YQTS > 0";
                SqlCommand cmd = new SqlCommand(sql, connstring);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                connstring.Close();
                connstring.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                connstring.Close();
                connstring.Dispose();
                return null;
            }
            finally
            {
                connstring.Close();
                connstring.Dispose();
            }
        }
    }
}
